import warnings
import os
import configparser
from openpyxl.workbook import Workbook
from openpyxl import load_workbook
from SysHelp import SysHelp as sh
from openpyxl.worksheet.worksheet import Worksheet

warnings.filterwarnings("ignore")

# 打印变量属性时,第一列和第二列的宽度
FIRST_WIDTH = 25
SECOND_WIDTH = 60
THIRD_WIDTH = 150


class ExcelHelp:
    def select_excel_files():
        return sh.list_dir(extensions=("xlsx", "xls"))

    def select_sheet(file: str = None, index: int = None):
        if file == None:
            file = ExcelHelp.select_excel_files()
        if not os.path.isfile(file):
            print("select_sheet 函数参数中文件路径有误!")
            return None
        wb = load_workbook(file)

        # 根据参数自动选择
        if not index is None and 0 <= index < len(wb.worksheets):
            return wb.worksheets[index]

        # 展示所有工作表名称供用户手动选择
        print("以下是所有可用的工作表：")
        for i, sheet in enumerate(wb.sheetnames, start=1):
            print(f"{i}. {sheet}")

        # 用户输入选择的工作表序号
        while True:
            sheet_index = int(input("请选择工作表的序号："))
            if 0 < sheet_index <= len(wb.sheetnames):
                selected_sheet = wb.sheetnames[sheet_index - 1]
                print(f"你选择的工作表是：{selected_sheet}")
                break
            else:
                print("输入的序号无效，请重新输入.")

        # 返回所选工作表对象
        return wb[selected_sheet]

    def find_data(sheet: Worksheet):
        """ 适合于表头为两列的成绩表找出具体成绩
            因为往往原始表格中掺杂了名次,原始分等

        Args:
            sheet (worksheet): 工作表对象

        Returns:
            _type_: 字典,有数据列名,列名下有两个参数:所在列,和起始行
        """
        data = {}
        # 搜索工作表中的所有合并单元格
        for index, merged_cell in enumerate(sheet.merged_cells.ranges):
            min_col = merged_cell.min_col
            max_col = merged_cell.max_col
            min_row = merged_cell.min_row
            max_row = merged_cell.max_row

            tmp = []
            if min_row == max_row:
                print(
                    f"\n< {sheet.cell(min_row, min_col).value} > 下方有 {max_col-min_col+1} 列,哪一列是你需要的数据,不需要直接按回车:"
                )
                for col in range(min_col, max_col + 1):
                    tmp.append(sheet.cell(min_row + 1, col).value)
                select = sh.select(tmp)
                if select != 0:
                    data[sheet.cell(min_row, min_col).value] = {
                        "col": min_col + select - 1,
                        "start_row": max_row + 2,
                    }
            elif min_col == max_col:
                print(
                    f"< {sheet.cell(min_row, min_col).value} > 是你需要的数据吗,需要请输入序号,不需要请按回车!"
                )
                tmp.append(sheet.cell(min_row, min_col).value)
                select = sh.select(tmp)
                if select != 0:
                    data[sheet.cell(min_row, min_col).value] = {
                        "col": min_col + select - 1,
                        "start_row": max_row + 1,
                    }
        sorted_data = sorted(data.items(), key=lambda kv: kv[1]["col"])
        return dict(sorted_data)

    def save_ini(file_name: str, data: dict):
        config = configparser.ConfigParser()
        for key, value in data.items():
            config[key] = value
        with open(file_name, "w", encoding="utf8") as f:
            config.write(f)

    def read_ini(file_name: str):
        try:
            config = configparser.ConfigParser()
            config.read("data.ini", encoding="utf8")
            return {
                section:
                {option: value
                 for option, value in config.items(section)}
                for section in config.sections()
            }
        except:
            print(f"解析 {file_name} 时出错了!")

    def copy_value(
            source: Worksheet = None,
            dest: Worksheet = None,
            source_range=(0, 0, 0, 0),
            dest_position=(0, 0),
    ):
        """从源工作表复制数据到目标工作表

        Args:
            source (worksheet): 源工作表
            dest (worksheet): 目标工作表
            source_range (tuple, optional): 源区域. Defaults to (row1, col1, row2, col2).
            dest_position (tuple, optional): 目标位置. Defaults to (row, col).
        """

        # 枚举行
        for row in range(source_range[0], source_range[2] + 1):
            # 枚举列
            for col in range(source_range[1], source_range[3] + 1):
                # 读取一个单元格
                source_value = source.cell(row=row, column=col).value
                # 计算目标行
                target_row = dest_position[0] + (row - source_range[0])
                # 计算目标列
                target_col = dest_position[1] + (col - source_range[1])
                # 复制
                dest.cell(row=target_row,
                          column=target_col).value = source_value

    def new_data_along_ini(ws_old: Worksheet, ini_name: str, exls_name: str):
        # 读数据
        area = ExcelHelp.read_ini(ini_name)
        # 建新表
        wb_new = Workbook()
        ws_new = wb_new.create_sheet("成绩表", 0)
        # 读ini,找出列数据
        data = ExcelHelp.read_ini(ini_name)
        # 按列复制
        for i, key in enumerate(data, start=1):
            # 表头
            ws_old.cell(int(data[key]["start_row"]),
                        int(data[key]["col"])).value = key
            # 数据
            ExcelHelp.copy_value(
                ws_old,
                ws_new,
                (
                    int(data[key]["start_row"]),
                    int(data[key]["col"]),
                    ws_old.max_row,
                    int(data[key]["col"]),
                ),
                (1, i),
            )
        wb_new.save(exls_name)
        
    def clean_sheet(sheet : Worksheet):
        cols = []
        for col in range(2, sheet.max_column + 1):
            num = 0
            for row in range(1, sheet.max_row + 1):
                try:
                    float(sheet.cell(row, col).value)
                    num += 1
                except:
                    pass
            if num >= 1:
                cols.append(col)
        for col in cols:
            for row in range(2, sheet.max_row + 1):
                try:
                    float(sheet.cell(row,col).value)
                except:
                    sheet.cell(row,col).value = ''